﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using AstFramework.Engine.Binding;
using AstFramework.Model;
using VulcanEngine.IR.Ast;
using VulcanEngine.IR.Ast.Table;
using VulcanEngine.IR.Ast.Task;
using VulcanEngine.IR.Ast.Transformation;

namespace AstLowerer.Capabilities
{
    public static class SlowlyChangingDimensionsLowerer
    {
        public static void ProcessTableScdColumns(AstTableNode astTableNode)
        {
            AstLowererValidation.ValidateScdTable(astTableNode);

            bool emitScdColumns = astTableNode.Columns.Any(column => column.ScdType == ScdType.Historical);
            if (emitScdColumns)
            {
                var scdFromColumn = new AstTableColumnNode(astTableNode) { Name = "_scdFrom", ColumnType = ColumnType.DateTime2, IsAutoGenerated = true };
                astTableNode.Columns.Add(scdFromColumn);

                var scdToColumn = new AstTableColumnNode(astTableNode) { Name = "_scdTo", ColumnType = ColumnType.DateTime2, IsAutoGenerated = true };
                astTableNode.Columns.Add(scdToColumn);
            }
        }

        public static void ProcessSlowlyChangingDimensionTransformations(SymbolTable symbolTable) ////HashSet<AstEtlRootNode> astEtlRootNodes, HashSet<AstTaskNode> astTaskNodes)
        {
            var snapshotSymbolTable = new List<IReferenceableItem>(symbolTable);
            foreach (var astNamedNode in snapshotSymbolTable)
            {
                if (astNamedNode.FirstThisOrParent<ITemplate>() == null)
                {
                    var destination = astNamedNode as AstDestinationNode;
                    if (destination != null)
                    {
                        if (destination.Table.HasScdColumns && !destination.DisableScd)
                        {
                            Utility.Replace(destination, CreateScdWorkflowFragment(destination.Table, destination.ParentItem, destination.InputPath == null ? null : destination.InputPath.OutputPath));
                        }
                    }

                    var merge = astNamedNode as AstMergeTaskNode;
                    if (merge != null)
                    {
                        var sourceTable = merge.TargetConstraint.ParentItem as AstTableNode;

                        // TODO: Must we do anything special for the UpdateTargetTable attribute?
                        if (sourceTable != null && sourceTable.HasScdColumns && !merge.DisableScd)
                        {
                            var targetTable = merge.TargetConstraint.ParentItem as AstTableNode;
                            if (targetTable == null)
                            {
                                continue;
                            }

                            var scdMergeEtl = new AstEtlRootNode(merge.ParentItem) { Name = Utility.NameCleanerAndUniqifier(merge.Name + "_scdEtl") };

                            // TODO: Do we need to limit query to non-computed columns?
                            var scdMergeSource = new AstQuerySourceNode(scdMergeEtl)
                                                     {
                                                         Connection = sourceTable.Connection,
                                                         Name = Utility.NameCleanerAndUniqifier(merge.Name + "_scdEtlSource"),
                                                         Query = new AstVariableParameterMappingQueryNode(null) { Body = TableLowerer.EmitSelectAllStatement(sourceTable) }
                                                     };
                            scdMergeSource.Query.ParentItem = scdMergeSource;

                            scdMergeEtl.Transformations.Add(scdMergeSource);
                            foreach (var transformation in CreateScdWorkflowFragment(targetTable, scdMergeEtl, scdMergeSource.OutputPath))
                            {
                                scdMergeEtl.Transformations.Add(transformation);
                            }
                        }
                    }
                }
            }
        }

        private static List<AstTransformationNode> CreateScdWorkflowFragment(AstTableNode targetTable, IFrameworkItem parentItem, AstDataflowOutputPathNode outputPath)
        {
            var workflowFragment = new List<AstTransformationNode>();

            AstTableColumnBaseNode lateArrivingStatusColumn = targetTable.Columns.FirstOrDefault(item => item.Name == "_IsLate");

            var scd = new AstSlowlyChangingDimensionNode(parentItem);
            scd.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_scd");
            scd.Connection = targetTable.Connection;
            scd.CurrentRowWhere = "[_scdFrom] IS NOT NULL AND [_scdFrom] IS NULL";
            scd.EnableInferredMember = targetTable.LateArriving;
            scd.FailOnFixedAttributeChange = true;
            scd.FailOnLookupFailure = false;
            scd.IncomingRowChangeType = 1;
            scd.InferredMemberIndicator = lateArrivingStatusColumn;

            // TODO:
            foreach (var column in targetTable.Columns)
            {
                if (column.IsAssignable && !column.IsAutoGenerated)
                {
                    ScdColumnMappingType mappingType;
                    switch (column.ScdType)
                    {
                        case ScdType.Error: 
                            mappingType = ScdColumnMappingType.FixedAttribute; 
                            break;
                        case ScdType.Historical: 
                            mappingType = ScdColumnMappingType.HistoricalAttribute; 
                            break;
                        case ScdType.Key:
                            mappingType = ScdColumnMappingType.Key;
                            break;
                        case ScdType.Other:
                            mappingType = ScdColumnMappingType.Other;
                            break;
                        case ScdType.Update:
                            mappingType = ScdColumnMappingType.ChangingAttribute;
                            break;
                        default:
                            mappingType = ScdColumnMappingType.Other; 
                            break;
                    }

                    scd.Mappings.Add(new AstScdTypeColumnMappingNode(scd) { MappingType = mappingType, QueryColumnName = column.Name });
                }
            }

            scd.Query = TableLowerer.EmitSelectAllStatement(targetTable);
            if (outputPath != null)
            {
                scd.InputPath = new AstDataflowMappedInputPathNode(scd) { OutputPath = outputPath };
            }

            workflowFragment.Add(scd);

            // Late Arriving Path
            if (targetTable.LateArriving)
            {
                BuildLateArrivingPath(targetTable, parentItem, workflowFragment, scd.InferredMemberPath);
            }

            // Change Path
            BuildChangePath(targetTable, parentItem, workflowFragment, scd.ChangingAttributePath);

            // Historical Path
            var historicalOutput = BuildHistoricalSubpath(targetTable, parentItem, workflowFragment, scd.HistoricalAttributePath);
            
            // Union Historical and New Paths
            var insertUnionAll = new AstUnionAllNode(parentItem) { Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertUnionAll") };
            insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll) { OutputPath = scd.NewPath });
            insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll) { OutputPath = historicalOutput });
            workflowFragment.Add(insertUnionAll);

            // Insert Path
            BuildInsertPath(targetTable, parentItem, workflowFragment, insertUnionAll.OutputPath);

            return workflowFragment;
        }

        private static void BuildLateArrivingPath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdLateArrivingPath)
        {
            var inferredCommand = new AstOleDBCommandNode(parentItem)
                                      {
                                          Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InferredCommand"), 
                                          Connection = targetTable.Connection
                                      };
            inferredCommand.Query = new AstTransformationMappedQueryNode(inferredCommand);
            int inferredIndex = 0;
            var inferredSetColumnValueMappings = new List<TableColumnValueMapping>();
            
            foreach (var column in targetTable.Columns)
            {
                if (column.IsAssignable && !column.IsAutoGenerated)
                {
                    inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand) { SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++) });
                    inferredSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
                }
            }

            if (targetTable.LateArriving)
            {
                inferredSetColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.Assign));
            }

            var inferredWhereColumnValueMappings = new List<TableColumnValueMapping>();
            foreach (var keyColumn in targetTable.PreferredKey.Columns)
            {
                inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++) });
                inferredWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
            }

            if (targetTable.LateArriving)
            {
                inferredWhereColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.CompareIsNot));
            }

            inferredCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, inferredSetColumnValueMappings, inferredWhereColumnValueMappings);
            inferredCommand.ValidateExternalMetadata = false;
            inferredCommand.InputPath = new AstDataflowMappedInputPathNode(inferredCommand) { OutputPath = scdLateArrivingPath };

            workflowFragment.Add(inferredCommand);
        }

        private static void BuildChangePath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdChangePath)
        {
            // Change Path should only be built if we actually have a column that takes a Type-1 Update

            bool doUpdate = false;
            foreach (var column in targetTable.Columns)
            {
                if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
                {
                    doUpdate = true;
                    break;
                }
            }
            if (doUpdate)
            {
                var changeCommand = new AstOleDBCommandNode(parentItem)
                                        {
                                            Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_ChangeCommand"),
                                            Connection = targetTable.Connection
                                        };
                changeCommand.Query = new AstTransformationMappedQueryNode(changeCommand);
                int changeIndex = 0;
                var changeSetColumnValueMappings = new List<TableColumnValueMapping>();
                foreach (var column in targetTable.Columns)
                {
                    if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
                    {
                        changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand) { SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++) });
                        changeSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
                    }
                }

                var changeWhereColumnValueMappings = new List<TableColumnValueMapping>();
                foreach (var keyColumn in targetTable.PreferredKey.Columns)
                {
                    changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++) });
                    changeWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
                }

                changeWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
                changeCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, changeSetColumnValueMappings, changeWhereColumnValueMappings);
                changeCommand.ValidateExternalMetadata = false;
                changeCommand.InputPath = new AstDataflowMappedInputPathNode(changeCommand) { OutputPath = scdChangePath };

                workflowFragment.Add(changeCommand);
            }
        }

        private static AstDataflowOutputPathNode BuildHistoricalSubpath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdHistoricalPath)
        {
            var historicalTransform = new AstDerivedColumnListNode(parentItem);
            historicalTransform.InputPath = new AstDataflowMappedInputPathNode(historicalTransform) { OutputPath = scdHistoricalPath };
            historicalTransform.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalDerivedColumns");
            historicalTransform.Columns.Add(new AstDerivedColumnNode(historicalTransform)
            {
                Name = "_scdTo",
                Expression = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
                Scale = 7,
                DerivedColumnType = ColumnType.DateTime2,
                ReplaceExisting = false
            });
            workflowFragment.Add(historicalTransform);

            var historicalCommand = new AstOleDBCommandNode(parentItem)
                                        {
                                            Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalCommand"), 
                                            Connection = targetTable.Connection
                                        };
            historicalCommand.Query = new AstTransformationMappedQueryNode(historicalCommand);

            var historicalSetColumnValueMappings = new List<TableColumnValueMapping>();
            var historicalWhereColumnValueMappings = new List<TableColumnValueMapping>();
            historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand) { SourceName = "_scdTo", TargetName = "Param_0" });
            historicalSetColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "?", MappingOperator.Assign));

            historicalWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
            int historicalIndex = 1;
            foreach (var keyColumn in targetTable.PreferredKey.Columns)
            {
                historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", historicalIndex++) });
                historicalWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
            }

            historicalCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, historicalSetColumnValueMappings, historicalWhereColumnValueMappings);
            historicalCommand.ValidateExternalMetadata = false;
            workflowFragment.Add(historicalCommand);

            return historicalCommand.OutputPath;
        }

        private static void BuildInsertPath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdInsertPath)
        {
            var insertTransform = new AstDerivedColumnListNode(parentItem) { Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDerivedColumns") };
            insertTransform.InputPath = new AstDataflowMappedInputPathNode(insertTransform) { OutputPath = scdInsertPath };
            insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
            {
                Name = "_scdFrom",
                Expression = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
                Scale = 7,
                DerivedColumnType = ColumnType.DateTime2,
                ReplaceExisting = false
            });
            insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
            {
                Name = "_scdTo",
                Expression = "NULL(DT_DBTIMESTAMP2,7)",
                Scale = 7,
                DerivedColumnType = ColumnType.DateTime2,
                ReplaceExisting = false
            });
            workflowFragment.Add(insertTransform);

            var insertDestination = new AstDestinationNode(parentItem);
            insertDestination.Table = targetTable;
            insertDestination.DisableScd = true;
            insertDestination.ValidateExternalMetadata = false;
            insertDestination.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDestination");
            workflowFragment.Add(insertDestination);
        }
    }
}
